Expeditions Dataset

The expeditions/ dataset anchors the entire spatial structure of the Pristine Seas Science Database. It provides a canonical registry of expeditions, spatial hierarchy, and field deployments across all survey methods. These tables enable consistent joins, filtering, and spatial aggregation across datasets.

1 Tables

1.1 expeditions.info

This table stores metadata for each expedition conducted by the Pristine Seas team. Each row corresponds to a unique expedition and defines the top-level context for all associated sampling efforts.

Table 1: expeditions.info Table Schema

Field	Type	Required	Description
exp_id	STRING	true	Unique expedition identifier in the format ISO3_YEAR (e.g., FJI_2025)
number	INTEGER	true	Sequential expedition number (e.g., 43)
name	STRING	true	Official or working expedition name
country	STRING	true	Primary country or jurisdiction visited
start_date	DATE	true	Expedition start date in ISO 8601 format (YYYY-MM-DD)
end_date	DATE	true	Expedition end date in ISO 8601 format (YYYY-MM-DD)
lead	STRING	true	Overall expedition lead
science_lead	STRING	true	Lead scientist for the research campaign
vessel	STRING	false	Name of the vessel or platform used
ship_track	STRING	false	WKT LINESTRING representing the ship’s route (optional)
partners	STRING	false	Institutional collaborators (optional)
description	STRING	false	Brief description of expedition goals and scope
notes	STRING	false	Optional free-text notes or admin metadata

To date, our team has conducted 47 expeditions across 30 countries since our first one in 2007.

Figure 1: Timeline of expeditions

1.2 expeditions.sites

This table defines the primary spatial unit of deployment for each survey method. A site represents a discrete application of a method (e.g., UVS, pBRUV, submersible) at a specific location and time during an expedition. Each method maintains its own internal sites table, but this shared expeditions.sites table serves as the canonical registry for cross-method integration, mapping, and analysis.

What is a site?

A site is defined by a unique combination of exp_id, method, and a 3-digit site number
(e.g., FJI_2025_uvs_001)

• It represents one deployment, diver, or survey event of a given method at a location
  
• Each site may contain multiple stations (e.g., depth strata, replicates, rigs)

Examples:

• A UVS site might be a reef where divers conduct fish, benthic, and invertebrate surveys at different depths
  
• A pBRUV site could be a pelagic deployment of five camera rigs
• A submersible site is typically a single dive with multiple horizontal transects

Sites are the parent unit of stations and the core join key for spatial summaries, mapping, and integration across methods.

All methods share the following core site schema (Table 2)

Table 2: expeditions.sites Table Schema

Field	Type	Required	Description
ps_site_id	STRING	true	Unique site ID (exp_id_method_###), e.g., FJI_2025_uvs_001
exp_id	STRING	true	Foreign key to expeditions.info
method	STRING	true	Field method used (e.g., uvs, pbruv, edna, sub)
region	STRING	true	Broad geographic or administrative unit (e.g., Murat, Chocó, Tuamotu, Duff Islands).
subregion	STRING	true	Intermediate feature within the region such as an atoll, island, gulf, or reef complex
locality	STRING	false	Local named feature such as a village, bay, cove, reef (e.g., Lolieng, Ensenada Utría).
date	DATE	true	Date of the site-level deployment in ISO 8601 format (YYYY-MM-DD)
time	TIME	true	Local time of the deployment in 24-hour format (HH:MM:SS)
latitude	FLOAT	true	Approximate latitude (decimal degrees, WGS84)
longitude	FLOAT	true	Approximate longitude (decimal degrees, WGS84)
lead	STRING	true	Name of the lead scientist or survey team lead
notes	STRING	false	Optional comments, metadata, or field observations